package cn.com.checne.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import cn.com.checne.model.domain.JobCategory;
import cn.com.checne.model.domain.JobDept;
import cn.com.checne.model.domain.JobInfo;
import cn.com.checne.model.domain.LanguageInfo;
import cn.com.checne.model.dto.JobQuery;
import cn.com.checne.util.TimeUtils;


@Repository
public class JobInfoDao {
	protected static final Logger log = Logger.getLogger(JobInfoDao.class);

	/**
	 * create
	 * @param cate
	 * @return
	 */
	public boolean create(JobInfo job) {
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String createSql = "insert into tbl_job(lg_id_fk,job_cate_id_fk,job_dept_id_fk," +
					"job_title,job_person,job_address,job_contact,job_responsibility,job_requirement,job_publishdate)" +
					" values(?,?,?,?,?,?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(createSql);
			int i = 0 ;
			pstmt.setInt(++i,job.getLanguageId());
			pstmt.setInt(++i,job.getCateId());
			pstmt.setInt(++i,job.getDeptId());
			pstmt.setString(++i,job.getTitle());
			pstmt.setInt(++i,job.getPerson());
			pstmt.setString(++i,job.getAddress());
			pstmt.setString(++i,job.getContact());
			pstmt.setString(++i,job.getResponsibility());
			pstmt.setString(++i,job.getRequirement());
			pstmt.setTimestamp(++i, new java.sql.Timestamp(job.getPublishDate().getTime()));
			pstmt.execute();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result;
	}

	/**
	 * @param cate
	 * @return
	 */
	public boolean update(JobInfo job) {
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "update tbl_job set " +
					"lg_id_fk = ?,job_cate_id_fk = ?,job_dept_id_fk=?,job_title = ?,job_person=?,job_address = ?," +
					"job_contact =? ,job_responsibility = ?,job_requirement =?,job_publishdate=? where job_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			int i = 0 ;
			pstmt.setInt(++i,job.getLanguageId());
			pstmt.setInt(++i,job.getCateId());
			pstmt.setInt(++i,job.getDeptId());
			pstmt.setString(++i,job.getTitle());
			pstmt.setInt(++i, job.getPerson());
			pstmt.setString(++i,job.getAddress());
			pstmt.setString(++i,job.getContact());
			pstmt.setString(++i,job.getResponsibility());
			pstmt.setString(++i,job.getRequirement());
			pstmt.setTimestamp(++i, new java.sql.Timestamp(job.getPublishDate().getTime()));
			pstmt.setInt(++i, job.getJobId());
			
			pstmt.executeUpdate();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result;
	}

	/**
	 * @param id
	 * @return
	 */
	public boolean delete(int id){
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "delete from tbl_job where job_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			int i = 0 ;
			pstmt.setInt(++i,id);
			log.info("[SQL]" + pstmt.toString());
			pstmt.executeUpdate();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result ;
	}
	/**
	 * 
	 * @param JobId
	 * @return
	 */
	public JobInfo getJobById(int jobId) {
		JobInfo job = new JobInfo();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_job where job_id = ? ";

			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, jobId);
			log.info("[SQL]" + pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			List<JobInfo> list = getResult(rs);
			job = (list != null && list.size() > 0 ) ? list.get(0) : job ;
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return job;
	}
	/**
	 * 
	 * @param query
	 * @param start
	 * @param num
	 * @return
	 */
	public List<JobInfo> getJobListByCondition(JobQuery query,int start,int num) {
		List<JobInfo> list = new ArrayList<JobInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_job where 1=1 " ;
			if(query.getLanguageId() > 0){
				sql += " and lg_id_fk = ? " ;
			}
			if(query.getCateId() > 0){
				sql += " and job_cate_id_fk = ? " ;
			}
			if(query.getDeptId() > 0){
				sql += " and job_dept_id_fk = ? " ;
			}
			if(StringUtils.isNotBlank(query.getKw())){
				sql += " and (job_title like ? or job_responsibility like ? or job_requirement like ?)";
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				sql += " and job_publishdate >= ? ";
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				sql += " and job_publishdate <= ? ";
			}
			sql += "order by job_publishdate desc limit ?,?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0 ;
			if(query.getLanguageId() > 0){
				pstmt.setInt(++i, query.getLanguageId());
			}
			if(query.getCateId() > 0){
				pstmt.setInt(++i, query.getCateId());
			}
			if(query.getDeptId() > 0){
				pstmt.setInt(++i, query.getDeptId());
			}
			if(StringUtils.isNotBlank(query.getKw())){
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getStartTime()).getTime()));
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getEndTime()).getTime()));
			}
			pstmt.setInt(++i, start);
			pstmt.setInt(++i, num);
			log.info("[SQL]" + pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			list.addAll(getResult(rs));
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 
	 * @param query
	 * @return
	 */
	public int getJobCountByCondition(JobQuery query) {
		int total = 0 ;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select count(1) as total from tbl_job where 1=1 " ;
			if(query.getLanguageId() > 0){
				sql += " and lg_id_fk = ? " ;
			}
			if(query.getCateId() > 0){
				sql += " and job_cate_id_fk = ? " ;
			}
			if(query.getDeptId() > 0){
				sql += " and job_dept_id_fk = ? " ;
			}
			if(StringUtils.isNotBlank(query.getKw())){
				sql += " and (job_title like ? or job_responsibility like ? or job_requirement like ?)";
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				sql += " and job_publishdate >= ? ";
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				sql += " and job_publishdate <= ? ";
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0 ;
			if(query.getLanguageId() > 0){
				pstmt.setInt(++i, query.getLanguageId());
			}
			if(query.getCateId() > 0){
				pstmt.setInt(++i, query.getCateId());
			}
			if(query.getDeptId() > 0){
				pstmt.setInt(++i, query.getDeptId());
			}
			if(StringUtils.isNotBlank(query.getKw())){
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getStartTime()).getTime()));
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getEndTime()).getTime()));
			}
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				total = rs.getInt("total");
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return total;
	}
	
	
	/**
	 * get rseult from resultSet
	 * @param rs
	 * @return
	 */
	public List<JobInfo> getResult(ResultSet rs) {
		List<JobInfo> list = new ArrayList<JobInfo>();
		try {
			JobInfo job = null ;
			while(rs.next()) {
				job = new JobInfo();
				job.setJobId(rs.getInt("job_id"));
				job.setTitle(rs.getString("job_title"));
				job.setPerson(rs.getInt("job_person"));
				job.setAddress(rs.getString("job_address"));
				job.setContact(rs.getString("job_contact"));
				job.setResponsibility(rs.getString("job_responsibility"));
				job.setRequirement(rs.getString("job_requirement"));
				job.setPublishDate(rs.getTimestamp("job_publishdate"));

				job.setLanguageId(rs.getInt("lg_id_fk"));
				LanguageInfo lg = new LanguageInfoDao().getLanguageInfoById(job.getLanguageId());
				job.setLanguageInfo(lg);
				
				job.setCateId(rs.getInt("job_cate_id_fk"));
				JobCategory cate = new JobCategoryDao().getJobCategory(job.getCateId());
				job.setJobCategory(cate);
				
				job.setDeptId(rs.getInt("job_dept_id_fk"));
				JobDept dept = new JobDeptDao().getJobDept(job.getDeptId());
				job.setJobDept(dept);
				
				list.add(job);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
}